﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using S2_1.Models;

namespace S2_1.Repositories
{
    public class UserRepository : IDisposable
    {
        private readonly SqlConnection _sqlConnection;
        public UserRepository()
        {
            //获取数据库连接字符串
            string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
            _sqlConnection = new SqlConnection(connStr);
            _sqlConnection.Open();
        }

        /// <summary>
        /// 获取所有产品
        /// </summary>
        /// <returns></returns>
        public IEnumerable<User> All()
        {
            var sql = @"SELECT [product_id]
                              ,[product_name]
                              ,[product_category]
                              ,[ingredients]
                              ,[current_price]
                              ,[production_cost]
                              ,[is_seasonal]
                              ,[is_currently_available]
                              ,[launch_date]
                          FROM [dbo].[products]";
            var products = new List<User>();
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var User = new User()
                        {
                            //ProductId = reader.GetInt32(0),
                            //ProductName = reader.GetString(1),
                            //ProductCategory = reader.GetString(2),
                            //Ingredients = reader.GetString(3),
                            //CurrentPrice = reader.GetDecimal(4),
                            //ProductionCost = reader.GetDecimal(5),
                            //IsSeasonal = reader.GetBoolean(6),
                            //IsCurrentlyAvailable = reader.GetBoolean(7),
                            //LaunchDate = reader.GetDateTime(8)
                        };
                        products.Add(User);
                    }
                }
            }
            return products;
        }

        /// <summary>
        /// 根据Id获取一个产品
        /// </summary>
        /// <param name="id">产品Id</param>
        /// <returns></returns>
        public User Single(string emial)
        {
            var sql = @"SELECT [Id]
                          ,[Emial]
                          ,[Password]
                          ,[FirstName]
                          ,[LastName]
                          ,[Phone]
                          ,[Picture]
                          ,[配送方式]
                          ,[安全问题]
                          ,[安全答案]
                      FROM [dbo].[User]
                      WHERE [Emial] = @emial";
            User User = null;
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@emial", emial);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        User = new User()
                        {
                            Id = reader.GetInt32(0),
                            Emial = reader.GetString(1),
                            Password = reader.GetString(2),
                            FirstName = reader.GetString(3),
                            LastName = reader.GetString(4),
                            Phone = reader.GetString(5),
                            Picture = reader.GetString(6),
                            配送方式 = reader.GetString(7),
                            安全问题 = reader.GetString(8),
                            安全答案 = reader.GetString(9),
                        };
                    }
                }

            }
            return User;
        }

        /// <summary>
        /// 根据Id获取一个产品
        /// </summary>
        /// <param name="id">产品Id</param>
        /// <returns></returns>
        public User Single(string emial, string password)
        {
            var sql = @"SELECT [Id]
                          ,[Emial]
                          ,[Password]
                          ,[FirstName]
                          ,[LastName]
                          ,[Phone]
                          ,[Picture]
                          ,[配送方式]
                          ,[安全问题]
                          ,[安全答案]
                      FROM [dbo].[User]
                      WHERE [Emial] = @emial AND [Password] = @password";
            User User = null;
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@emial", emial);
                cmd.Parameters.AddWithValue("@password", password);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        User = new User()
                        {
                            Id = reader.GetInt32(0),
                            Emial = reader.GetString(1),
                            Password = reader.GetString(2),
                            FirstName = reader.GetString(3),
                            LastName = reader.GetString(4),
                            Phone = reader.GetString(5),
                            Picture = reader.GetString(6),
                            配送方式 = reader.GetString(7),
                            安全问题 = reader.GetString(8),
                            安全答案 = reader.GetString(9),
                        };
                    }
                }

            }
            return User;
        }

        /// <summary>
        /// 添加一个新产品
        /// </summary>
        /// <param name="User">新产品对象</param>
        /// <returns>新增是否成功</returns>
        public bool Add(User User)
        {
            //product_id 主键。修改成自增
            var sql = @"INSERT INTO [dbo].[User]
                           ([Emial]
                           ,[Password]
                           ,[FirstName]
                           ,[LastName]
                           ,[安全问题]
                           ,[安全答案])
                     VALUES
                           (@email
                           ,@password
                           ,@firstName
                           ,@lastName
                           ,@安全问题
                           ,@安全答案)";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@email", User.Emial);
                cmd.Parameters.AddWithValue("@password", User.Password);
                cmd.Parameters.AddWithValue("@firstName", User.FirstName);
                cmd.Parameters.AddWithValue("@lastName", User.LastName);
                cmd.Parameters.AddWithValue("@安全问题", User.安全问题);
                cmd.Parameters.AddWithValue("@安全答案", User.安全答案);
                return cmd.ExecuteNonQuery() == 1;
            }
        }


        public bool UpdatePassword(string email, string password)
        {
            var sql = @"UPDATE [dbo].[User]  
                        SET [Password] = @password
                        WHERE [Emial] = @email";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@password", password);

                return cmd.ExecuteNonQuery() == 1;
            }

        }

        /// <summary>
        /// 更新产品
        /// </summary>
        /// <param name="id">更新产品的Id</param>
        /// <param name="User">更新产品对象</param>
        /// <returns>是否更新成功</returns>
        public bool Update(User User)
        {
            var sql = @"UPDATE [dbo].[products]
                           SET [product_name] = @product_name
                              ,[product_category] = @product_category
                              ,[ingredients] = @ingredients
                              ,[current_price] = @current_price
                              ,[production_cost] = @production_cost
                              ,[is_seasonal] = @is_seasonal
                              ,[is_currently_available] = @is_currently_available
                              ,[launch_date] = @launch_date
                         WHERE  [product_id] = @product_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                //cmd.Parameters.AddWithValue("@product_id", id);
                //cmd.Parameters.AddWithValue("@product_name", User.ProductName);
                //cmd.Parameters.AddWithValue("@product_category", User.ProductCategory);
                //cmd.Parameters.AddWithValue("@ingredients", User.Ingredients);
                //cmd.Parameters.AddWithValue("@current_price", User.CurrentPrice);
                //cmd.Parameters.AddWithValue("@production_cost", User.ProductionCost);
                //cmd.Parameters.AddWithValue("@is_seasonal", User.IsSeasonal);
                //cmd.Parameters.AddWithValue("@is_currently_available", User.IsCurrentlyAvailable);
                //cmd.Parameters.AddWithValue("@launch_date", User.LaunchDate);

                return cmd.ExecuteNonQuery() == 1;
            }
        }


        /// <summary>
        /// 按 ID 删除产品数据
        /// </summary>
        /// <param name="id">产品Id</param>
        /// <returns></returns>
        public bool RemoveSingle(int id)
        {
            var sql = @"DELETE [dbo].[products]
                         WHERE  [product_id] = @product_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@product_id", id);
                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public void Dispose()
        {
            _sqlConnection.Close();
            _sqlConnection.Dispose();
        }
    }
}